# -*- coding: utf8 -*-

from project.mysql.table import Table

class TableFile2file( Table ):

    _table_name = "file2file"

    def create( self ):
        cur = self.getCursor()

        # Create table
        cur.execute(
            "CREATE TABLE IF NOT EXISTS %s (" % self._table_name+
            "id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, "+
            "file1_id INTEGER UNSIGNED NOT NULL, "+
            "file2_id INTEGER UNSIGNED NOT NULL, "+
            "file1_count SMALLINT(4) UNSIGNED NOT NULL, "+
            "file2_count SMALLINT(4) UNSIGNED NOT NULL, "+
            "equal_images SMALLINT(4) UNSIGNED NOT NULL, "+
            "quality_points SMALLINT NOT NULL) ENGINE %s" % self.getEngine()
        )
        try:
            cur.execute(
                "ALTER TABLE `%s` ADD INDEX files (file1_id, file2_id)" % self._table_name
            )
            cur.execute(
                "ALTER TABLE `%s` ADD INDEX files_count (file1_count, file2_count, equal_images, quality_points)" % self._table_name
            )
        except:
            pass
        cur.close()
        return


    def insert( self, file1_id, file2_id, file1_count, file2_count):
        cur = self.getCursor()

        rows = cur.execute(
            "INSERT INTO %s (" % self._table_name+
            "file1_id, file2_id, file1_count, file2_count, equal_images, quality_points"+
            ") VALUES ("+
            "%(file1_id)s, %(file2_id)s, %(file1_count)s, %(file2_count)s, %(equal_images)s, %(quality_points)s"+
            ")", {
                "file1_id":file1_id,
                "file2_id":file2_id,
                "file1_count":file1_count,
                "file2_count":file2_count,
                "equal_images":1,
                "quality_points":0
            }
        )
        self.getConnection().commit()

        last_id = None
        if rows:
            last_id = self.getConnection().insert_id()

        cur.close()
        return last_id

    def has(self, file1_id, file2_id):
        cur = self.getCursor()
        cur.execute(
            "SELECT * FROM file2file WHERE file1_id=%(id1)s AND file2_id=%(id2)s",
            {"id1": file1_id, "id2":file2_id}
        )
        item = cur.fetchone()
        if not item:
            cur.execute(
                "SELECT * FROM file2file WHERE file1_id=%(id2)s AND file2_id=%(id1)s",
                    {"id1": file1_id, "id2":file2_id}
            )
            item = cur.fetchone()
            if not item:
                item = False
            else:
                item["reverse"] = True
        else:
            item["reverse"] = False

        cur.close()
        return item

    def remove_file(self, file_id):
        cur = self.getCursor()
        rc = cur.execute(
            "DELETE FROM file2file WHERE file1_id=%(id)s OR file2_id=%(id)s",
                {"id": file_id}
        )
        self.getConnection().commit()
        cur.close()
        return rc

    def inc( self, f2f_id, quality):
        cur = self.getCursor()
        rc = cur.execute(
            "UPDATE file2file SET equal_images=equal_images+1, quality_points=quality_points+%(quality)s "+
            "WHERE id=%(id)s",
            {"id":f2f_id, "quality":quality}
        )
        self.getConnection().commit()
        cur.close()
        return rc

    def get_equal_files(self, diff):
        cur = self.getCursor()
        cur.execute(
            "SELECT * FROM file2file WHERE "+
            "( file1_count>%(diff)s AND ABS(file1_count-equal_images)<%(diff)s) OR "+
            "( file2_count>%(diff)s AND ABS(file2_count-equal_images)<%(diff)s",
                {"diff": diff}
        )
        item = cur.fetchone()
        cur.close()
        if not item:
            item = False
        return item

    def get_count(self):
        cur = self.getCursor()
        cur.execute("SELECT COUNT(1) as c FROM file2file")
        item = cur.fetchone()
        cur.close()
        if not item:
            item = 0
        return item['c']

    def get_file2file(self, limit, offset):
        cur = self.getCursor()
        cur.execute("SELECT * FROM file2file WHERE id BETWEEN %(o1)s AND %(o2)s ORDER BY id",
                {"o1":offset,"o2":offset+limit-1})
        items = cur.fetchall()
        cur.close()
        return items

    pass
